﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using InfoLayer;

namespace DataAccessLayer
{
    public class PhongDataAccess:DataAccessAbstract
    {
        private OleDbConnection _cnn;
        private OleDbDataAdapter _da;
        private OleDbCommand _cmd;
        private OleDbDataReader _dr;
        private String _sqlString;

        public ArrayList LayDanhSach()
        {
            ArrayList kq = new ArrayList();
            PhongInfo Info;
            _sqlString = "Select * from PHONG";
            //khoi tao bien ket noi CSDL
            _cnn = ConnectionData();
            //chuoi ket noi CSDL
            _cmd = new OleDbCommand(_sqlString, _cnn);
            _dr = _cmd.ExecuteReader();
            //doc du lieu tu CSDL
            while (_dr.Read())
            {
                Info = new PhongInfo();
                try
                {
                    Info.TinhTrangPhong = Convert.ToBoolean(_dr["TinhTrangPhong"].ToString());
                }
                catch (System.Exception ex)
                {
                    _cnn.Close();
                    throw new Exception("Lỗi kết nối dữ liệu.");
                }
                kq.Add(Info);
            }
            _cnn.Close();
            return kq;
        }

        public DataTable LayBangDanhSachPhong()
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select * from PHONG";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public void Insert(ref PhongInfo phongInfo)
        {
            //viet cau lenh SQL
            _sqlString = "INSERT INTO PHONG (MaPhong, TenPhong, MaLoaiPhong, TinhTrangPhong, GhiChu) VALUES ('" +
                phongInfo.MaPhong + "','" +
                phongInfo.TenPhong + "','" +
                phongInfo.MaLoaiPhong + "'," +
                phongInfo.TinhTrangPhong + ",'" +
                phongInfo.GhiChu + "')";
            //ket noi CSDL
            _cnn = ConnectionData();
            try
            {
                //thu thi cau truy van SQL
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();
                
            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi kết nối cơ sở dữ liệu.");
            }
            _cnn.Close();
        }

        public void Update(PhongInfo phongInfo)
        {
            _sqlString = "UPDATE PHONG SET " +
                                "TenPhong = '" + phongInfo.TenPhong + "'," +
                                "MaLoaiPhong = '" + phongInfo.MaLoaiPhong + "'," +
                                "TinhTrangPhong = " + phongInfo.TinhTrangPhong + "," +
                                "GhiChu = '" + phongInfo.GhiChu + "'" +
                                "WHERE MaPhong = '" + phongInfo.MaPhong +"'";
            _cnn = ConnectionData();
            try
            {
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi Update dữ liệu.");
            }
            _cnn.Close();
        }

        public void Delete(PhongInfo phongInfo)
        {
            _sqlString = "delete from PHONG where MaPhong = '" + phongInfo.MaPhong + "'";
            _cnn = ConnectionData();
            try
            {
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi xóa dữ liệu.");
            }
            _cnn.Close();
        }

        public string TaoMaTuDong(string maGoc, int batDau)
        {
            string str, kq = "";
            int so;
            try
            {
                str = maGoc.Substring(batDau);
                kq = maGoc.Substring(0, batDau);
                so = Convert.ToInt32(str) + 1;
                for (int i = 0; i < str.Length - so.ToString().Length; i++)
                    kq += "0";
                kq = kq + so.ToString();
                return kq;
            }
            catch //(System.Exception ex)
            {
                return null;
            }
        }

        public DataTable LayDonGia(LoaiPhongInfo loaiPhongInfo, String _MaLoaiPhong)
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select DonGiaPhong from LOAIPHONG where LOAIPHONG.MaLoaiPhong = '"+ _MaLoaiPhong + "'";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public DataTable LayDSPhongTrong()
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select * from PHONG where PHONG.TinhTrangPhong = false";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public DataTable LayDSPhongDaThue()
        {
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select * from PHONG where PHONG.TinhTrangPhong = true";
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }

        public void Update_TinhTrangPhong(String _MaPhong, bool isTinhTrangPhong)
        {
            //true: da thue phong
            bool tinhTrang = true;
            _sqlString = "UPDATE PHONG SET " +
                                "TinhTrangPhong = " + isTinhTrangPhong +
                                " WHERE MaPhong = '" + _MaPhong + "'";
            _cnn = ConnectionData();
            try
            {
                _cmd = new OleDbCommand(_sqlString, _cnn);
                _cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                _cnn.Close();
                throw new Exception("Lỗi Update dữ liệu.");
            }
            _cnn.Close();
        }

         

        public DataTable Timkiemphong(PhongInfo phonginfo, bool isCheck)
        {
            bool isChoose = false;
            DataTable DSPHONG = new DataTable();
            _cnn = ConnectionData();
            _sqlString = "Select * from PHONG where";

            if (phonginfo.MaPhong != "")
            {
                _sqlString += " PHONG.MaPhong = '" + phonginfo.MaPhong + "'";
                isChoose = true;
            }
            if (phonginfo.TenPhong != "")
            {
                if (isChoose == true)
                    _sqlString += " and  PHONG.TenPhong = '" + phonginfo.TenPhong + "'";
                else
                    _sqlString += " PHONG.TenPhong = '" + phonginfo.TenPhong + "'";
                isChoose = true;
            }
            if (phonginfo.MaLoaiPhong != "")
            {
                if (isChoose == true)
                    _sqlString += " and  PHONG.MaLoaiphong = '" + phonginfo.MaLoaiPhong + "'";
                else
                    _sqlString += " PHONG.MaLoaiphong = '" + phonginfo.MaLoaiPhong + "'";
                isChoose = true;
            }

            if (isCheck == true)
            {
                if (phonginfo.TinhTrangPhong == false)
                    if (isChoose == true)
                        _sqlString += " and  PHONG.TinhTrangPhong = false";// +phonginfo.TinhTrangPhong + " ";
                    else
                        _sqlString += " PHONG.TinhTrangPhong = false";
                else
                    if (isChoose == true)
                        _sqlString += " and  PHONG.TinhTrangPhong = true";
                    else
                        _sqlString += " PHONG.TinhTrangPhong = true";

            }
            _da = new OleDbDataAdapter(_sqlString, _cnn);
            _da.Fill(DSPHONG);
            _cnn.Close();
            return DSPHONG;
        }




    }
}
